﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using IPR_StudyMeetingMrgSys.DBUtility;//Please add references
namespace IPR_StudyMeetingMrgSys.DAL
{
	/// <summary>
	/// 数据访问类:TrainFiles
	/// </summary>
	public partial class TrainFiles
	{
		public TrainFiles()
		{}
		#region  BasicMethod



		/// <summary>
		/// 增加一条数据
		/// </summary>
		public long Add(IPR_StudyMeetingMrgSys.Model.TrainFiles model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into TrainFiles(");
			strSql.Append("TFParentID,TFName,FTID,KKID,TFPath,TFSize,TFMD5,TFUseCount,TFIsPhoto,TFIsPass,TFAddUserID,TFAddUserName,TFAddDateTime,TFUploadDeptID,TFServerPath,TFFileKey,TFFileLevel,TFReadTime,TFIsUse,TFDownCount,DepID,TFID,TFEndDateTime)");
			strSql.Append(" values (");
			strSql.Append("@TFParentID,@TFName,@FTID,@KKID,@TFPath,@TFSize,@TFMD5,@TFUseCount,@TFIsPhoto,@TFIsPass,@TFAddUserID,@TFAddUserName,@TFAddDateTime,@TFUploadDeptID,@TFServerPath,@TFFileKey,@TFFileLevel,@TFReadTime,@TFIsUse,@TFDownCount,@DepID,@TFID,@TFEndDateTime)");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@TFParentID", SqlDbType.BigInt,8),
					new SqlParameter("@TFName", SqlDbType.NVarChar,255),
					new SqlParameter("@FTID", SqlDbType.Int,4),
					new SqlParameter("@KKID", SqlDbType.Int,4),
					new SqlParameter("@TFPath", SqlDbType.NVarChar,1024),
					new SqlParameter("@TFSize", SqlDbType.Int,4),
					new SqlParameter("@TFMD5", SqlDbType.NVarChar,50),
					new SqlParameter("@TFUseCount", SqlDbType.Int,4),
					new SqlParameter("@TFIsPhoto", SqlDbType.Int,4),
					new SqlParameter("@TFIsPass", SqlDbType.Int,4),
					new SqlParameter("@TFAddUserID", SqlDbType.Int,4),
					new SqlParameter("@TFAddUserName", SqlDbType.NVarChar,30),
					new SqlParameter("@TFAddDateTime", SqlDbType.DateTime,3),
					new SqlParameter("@TFUploadDeptID", SqlDbType.Int,4),
					new SqlParameter("@TFServerPath", SqlDbType.NVarChar,500),
					new SqlParameter("@TFFileKey", SqlDbType.NVarChar,50),
					new SqlParameter("@TFFileLevel", SqlDbType.NVarChar,30),
					new SqlParameter("@TFReadTime", SqlDbType.Int,4),
					new SqlParameter("@TFIsUse", SqlDbType.Int,4),
					new SqlParameter("@TFDownCount", SqlDbType.Int,4),
					new SqlParameter("@DepID", SqlDbType.NVarChar,200),
                    new SqlParameter("@TFID", SqlDbType.Int,4),
                    new SqlParameter("@TFEndDateTime", SqlDbType.DateTime,3)};
			parameters[0].Value = model.TFParentID;
			parameters[1].Value = model.TFName;
			parameters[2].Value = model.FTID;
			parameters[3].Value = model.KKID;
			parameters[4].Value = model.TFPath;
			parameters[5].Value = model.TFSize;
			parameters[6].Value = model.TFMD5;
			parameters[7].Value = model.TFUseCount;
			parameters[8].Value = model.TFIsPhoto;
			parameters[9].Value = model.TFIsPass;
			parameters[10].Value = model.TFAddUserID;
			parameters[11].Value = model.TFAddUserName;
			parameters[12].Value = model.TFAddDateTime;
			parameters[13].Value = model.TFUploadDeptID;
			parameters[14].Value = model.TFServerPath;
			parameters[15].Value = model.TFFileKey;
			parameters[16].Value = model.TFFileLevel;
			parameters[17].Value = model.TFReadTime;
			parameters[18].Value = model.TFIsUse;
			parameters[19].Value = model.TFDownCount;
            parameters[20].Value = model.DepID;
            parameters[21].Value = model.TFID;
            parameters[22].Value = Convert.ToDateTime( model.TFEndDateTime).Year==9999?null:model.TFEndDateTime;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt64(obj);
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(IPR_StudyMeetingMrgSys.Model.TrainFiles model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update TrainFiles set ");
			strSql.Append("AutoID=@AutoID,");
			strSql.Append("TFParentID=@TFParentID,");
			strSql.Append("TFName=@TFName,");
			strSql.Append("FTID=@FTID,");
			strSql.Append("KKID=@KKID,");
			strSql.Append("TFPath=@TFPath,");
			strSql.Append("TFSize=@TFSize,");
			strSql.Append("TFMD5=@TFMD5,");
			strSql.Append("TFUseCount=@TFUseCount,");
			strSql.Append("TFIsPhoto=@TFIsPhoto,");
			strSql.Append("TFIsPass=@TFIsPass,");
			strSql.Append("TFAddUserID=@TFAddUserID,");
			strSql.Append("TFAddUserName=@TFAddUserName,");
			strSql.Append("TFAddDateTime=@TFAddDateTime,");
            strSql.Append("TFEndDateTime=@TFEndDateTime,");
            strSql.Append("TFUploadDeptID=@TFUploadDeptID,");
			strSql.Append("TFServerPath=@TFServerPath,");
			strSql.Append("TFFileKey=@TFFileKey,");
			strSql.Append("TFFileLevel=@TFFileLevel,");
			strSql.Append("TFReadTime=@TFReadTime,");
			strSql.Append("TFIsUse=@TFIsUse,");
			strSql.Append("TFDownCount=@TFDownCount,");
			strSql.Append("DepID=@DepID");
			strSql.Append(" where TFID=@TFID");
			SqlParameter[] parameters = {
					new SqlParameter("@AutoID", SqlDbType.BigInt,8),
					new SqlParameter("@TFParentID", SqlDbType.BigInt,8),
					new SqlParameter("@TFName", SqlDbType.NVarChar,255),
					new SqlParameter("@FTID", SqlDbType.Int,4),
					new SqlParameter("@KKID", SqlDbType.Int,4),
					new SqlParameter("@TFPath", SqlDbType.NVarChar,1024),
					new SqlParameter("@TFSize", SqlDbType.Int,4),
					new SqlParameter("@TFMD5", SqlDbType.NVarChar,50),
					new SqlParameter("@TFUseCount", SqlDbType.Int,4),
					new SqlParameter("@TFIsPhoto", SqlDbType.Int,4),
					new SqlParameter("@TFIsPass", SqlDbType.Int,4),
					new SqlParameter("@TFAddUserID", SqlDbType.Int,4),
					new SqlParameter("@TFAddUserName", SqlDbType.NVarChar,30),
					new SqlParameter("@TFAddDateTime", SqlDbType.DateTime,3),
					new SqlParameter("@TFUploadDeptID", SqlDbType.Int,4),
					new SqlParameter("@TFServerPath", SqlDbType.NVarChar,500),
					new SqlParameter("@TFFileKey", SqlDbType.NVarChar,50),
					new SqlParameter("@TFFileLevel", SqlDbType.NVarChar,30),
					new SqlParameter("@TFReadTime", SqlDbType.Int,4),
					new SqlParameter("@TFIsUse", SqlDbType.Int,4),
					new SqlParameter("@TFDownCount", SqlDbType.Int,4),
					new SqlParameter("@DepID", SqlDbType.NVarChar,200),
					new SqlParameter("@TFID", SqlDbType.BigInt,8),
                    new SqlParameter("@TFEndDateTime", SqlDbType.DateTime,3)};
			parameters[0].Value = model.AutoID;
			parameters[1].Value = model.TFParentID;
			parameters[2].Value = model.TFName;
			parameters[3].Value = model.FTID;
			parameters[4].Value = model.KKID;
			parameters[5].Value = model.TFPath;
			parameters[6].Value = model.TFSize;
			parameters[7].Value = model.TFMD5;
			parameters[8].Value = model.TFUseCount;
			parameters[9].Value = model.TFIsPhoto;
			parameters[10].Value = model.TFIsPass;
			parameters[11].Value = model.TFAddUserID;
			parameters[12].Value = model.TFAddUserName;
			parameters[13].Value = model.TFAddDateTime;
			parameters[14].Value = model.TFUploadDeptID;
			parameters[15].Value = model.TFServerPath;
			parameters[16].Value = model.TFFileKey;
			parameters[17].Value = model.TFFileLevel;
			parameters[18].Value = model.TFReadTime;
			parameters[19].Value = model.TFIsUse;
			parameters[20].Value = model.TFDownCount;
			parameters[21].Value = model.DepID;
            parameters[22].Value = model.TFID;
            parameters[23].Value = model.TFEndDateTime;

            int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(long TFID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update  TrainFiles set TFIsUse=0");
			strSql.Append(" where TFID=@TFID");
			SqlParameter[] parameters = {
					new SqlParameter("@TFID", SqlDbType.BigInt)
			};
			parameters[0].Value = TFID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string TFIDlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update  TrainFiles set TFIsUse=0 ");
			strSql.Append(" where TFID in ("+TFIDlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public IPR_StudyMeetingMrgSys.Model.TrainFiles GetModel(long TFID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 AutoID,TFID,TFParentID,TFName,FTID,KKID,TFPath,TFSize,TFMD5,TFUseCount,TFIsPhoto,TFIsPass,TFAddUserID,TFAddUserName,TFAddDateTime,TFUploadDeptID,TFServerPath,TFFileKey,TFFileLevel,TFReadTime,TFIsUse,TFDownCount,DepID,TFEndDateTime from TrainFiles ");
			strSql.Append(" where TFID=@TFID");
			SqlParameter[] parameters = {
					new SqlParameter("@TFID", SqlDbType.BigInt)
			};
			parameters[0].Value = TFID;

			IPR_StudyMeetingMrgSys.Model.TrainFiles model=new IPR_StudyMeetingMrgSys.Model.TrainFiles();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public IPR_StudyMeetingMrgSys.Model.TrainFiles DataRowToModel(DataRow row)
		{
			IPR_StudyMeetingMrgSys.Model.TrainFiles model=new IPR_StudyMeetingMrgSys.Model.TrainFiles();
			if (row != null)
			{
				if(row["AutoID"]!=null && row["AutoID"].ToString()!="")
				{
					model.AutoID=long.Parse(row["AutoID"].ToString());
				}
				if(row["TFID"]!=null && row["TFID"].ToString()!="")
				{
					model.TFID=long.Parse(row["TFID"].ToString());
				}
				if(row["TFParentID"]!=null && row["TFParentID"].ToString()!="")
				{
					model.TFParentID=long.Parse(row["TFParentID"].ToString());
				}
				if(row["TFName"]!=null)
				{
					model.TFName=row["TFName"].ToString();
				}
				if(row["FTID"]!=null && row["FTID"].ToString()!="")
				{
					model.FTID=int.Parse(row["FTID"].ToString());
				}
				if(row["KKID"]!=null && row["KKID"].ToString()!="")
				{
					model.KKID=int.Parse(row["KKID"].ToString());
				}
				if(row["TFPath"]!=null)
				{
					model.TFPath=row["TFPath"].ToString();
				}
				if(row["TFSize"]!=null && row["TFSize"].ToString()!="")
				{
					model.TFSize=int.Parse(row["TFSize"].ToString());
				}
				if(row["TFMD5"]!=null)
				{
					model.TFMD5=row["TFMD5"].ToString();
				}
				if(row["TFUseCount"]!=null && row["TFUseCount"].ToString()!="")
				{
					model.TFUseCount=int.Parse(row["TFUseCount"].ToString());
				}
				if(row["TFIsPhoto"]!=null && row["TFIsPhoto"].ToString()!="")
				{
					model.TFIsPhoto=int.Parse(row["TFIsPhoto"].ToString());
				}
				if(row["TFIsPass"]!=null && row["TFIsPass"].ToString()!="")
				{
					model.TFIsPass=int.Parse(row["TFIsPass"].ToString());
				}
				if(row["TFAddUserID"]!=null && row["TFAddUserID"].ToString()!="")
				{
					model.TFAddUserID=int.Parse(row["TFAddUserID"].ToString());
				}
				if(row["TFAddUserName"]!=null)
				{
					model.TFAddUserName=row["TFAddUserName"].ToString();
                }
                if (row["TFAddDateTime"] != null && row["TFAddDateTime"].ToString() != "")
                {
                    model.TFAddDateTime = DateTime.Parse(row["TFAddDateTime"].ToString());
                }
                if (row["TFEndDateTime"] != null && row["TFEndDateTime"].ToString() != "")
                {
                    model.TFEndDateTime = DateTime.Parse(row["TFEndDateTime"].ToString());
                }
                if (row["TFUploadDeptID"]!=null && row["TFUploadDeptID"].ToString()!="")
				{
					model.TFUploadDeptID=int.Parse(row["TFUploadDeptID"].ToString());
				}
				if(row["TFServerPath"]!=null)
				{
					model.TFServerPath=row["TFServerPath"].ToString();
				}
				if(row["TFFileKey"]!=null)
				{
					model.TFFileKey=row["TFFileKey"].ToString();
				}
				if(row["TFFileLevel"]!=null)
				{
					model.TFFileLevel=row["TFFileLevel"].ToString();
				}
				if(row["TFReadTime"]!=null && row["TFReadTime"].ToString()!="")
				{
					model.TFReadTime=int.Parse(row["TFReadTime"].ToString());
				}
				if(row["TFIsUse"]!=null && row["TFIsUse"].ToString()!="")
				{
					model.TFIsUse=int.Parse(row["TFIsUse"].ToString());
				}
				if(row["TFDownCount"]!=null && row["TFDownCount"].ToString() != "")
				{
					model.TFDownCount=int.Parse( row["TFDownCount"].ToString()) ;
				}
				if(row["DepID"]!=null)
				{
					model.DepID=row["DepID"].ToString();
				}
			}
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select AutoID,TFID,TFParentID,TFName,FTID,KKID,TFPath,TFSize,TFMD5,TFUseCount,TFIsPhoto,TFIsPass,TFAddUserID,TFAddUserName,TFAddDateTime,TFUploadDeptID,TFServerPath,TFFileKey,TFFileLevel,TFReadTime,TFIsUse,TFDownCount,DepID,TFEndDateTime ");
			strSql.Append(" FROM TrainFiles ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" AutoID,TFID,TFParentID,TFName,FTID,KKID,TFPath,TFSize,TFMD5,TFUseCount,TFIsPhoto,TFIsPass,TFAddUserID,TFAddUserName,TFAddDateTime,TFUploadDeptID,TFServerPath,TFFileKey,TFFileLevel,TFReadTime,TFIsUse,TFDownCount,DepID,TFEndDateTime ");
			strSql.Append(" FROM TrainFiles ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM TrainFiles ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.TFID desc");
			}
			strSql.Append(")AS Row, T.*  from TrainFiles T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString());
		}

        /*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "TrainFiles";
			parameters[1].Value = "TFID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

        #endregion  BasicMethod
        #region  ExtensionMethod
        /// <summary>
        /// 文件审核
        /// </summary>
        /// <param name="tfid"></param>
        /// <returns></returns>
        public bool FileAudit(int tfid)
        {
            string sql = "  update TrainFiles set TFIsPass=1 where TFID=" + tfid.ToString();
            int rows = DbHelperSQL.ExecuteSql(sql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int TFID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from TrainFiles");
            strSql.Append(" where TFID=@TFID");
            SqlParameter[] parameters = {
                    new SqlParameter("@TFID", SqlDbType.Int,4)
            };
            parameters[0].Value = TFID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 是否存在该文件名称
        /// </summary>
        public bool Exists(string TFName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from TrainFiles");
            strSql.Append(" where TFName=@TFName");
            SqlParameter[] parameters = {
                    new SqlParameter("@TFName", SqlDbType.NVarChar,255)
            };
            parameters[0].Value = TFName;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, out int rowCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.TFID desc");
            }
            strSql.Append(")AS Row, TFID,T.DepID,TFParentID,TFName,T.FTID,T.KKID,TFPath,TFSize,TFMD5,TFUseCount,TFIsPhoto,case when TFIsPass=0 then '未审核' else '已审核' end as IsPass,");
            strSql.Append("TFAddUserID,TFAddUserName,CONVERT(varchar(20), TFAddDateTime,120) as TFAddDateTime , CONVERT(varchar(20), TFEndDateTime,120) as TFEndDateTime ,TFUploadDeptID, TFServerPath, ");
            strSql.Append("TFFileKey, TFFileLevel, TFReadTime,case when TFIsUse=1 then '未删除' else '已删除' end as IsDelete, b.KKName, c.DepName, d.FTName ,case when TFEndDateTime<CONVERT(varchar(10), GETDATE(),120) then '已到期' else '有效' end as IsValid ,ISNULL( TFDownCount  ,0) as TFDownCount");
            strSql.Append(" FROM TrainFiles T ");
            strSql.Append(" left join TrainFileKnowledgeKey b on T.KKID = b.KKID ");
            strSql.Append(" left join Dep c on T.TFUploadDeptID = c.DepID ");
            strSql.Append(" left join TrainFileType d on T.FTID = d.FTID ");
            if (!string.IsNullOrWhiteSpace(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");

            rowCount = 0;
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null)
                if (ds.Tables.Count > 0)
                    if (ds.Tables[0].Rows.Count > 0)
                        rowCount = ds.Tables[0].Rows.Count;

            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion  ExtensionMethod
    }
}

